USE ASLB 
GO
IF (OBJECTPROPERTY ( OBJECT_ID('MonitorASAvailability'), 'IsProcedure' ) IS NOT NULL)
BEGIN
   DROP PROC MonitorASAvailability
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[MonitorASAvailability] 
       
AS
/***********************************************************************************
** Name      : MonitorASAvailability
**
** File Name : MonitorASAvailability.sql
**
** Desc      : 
**
** Called By : 
**
** Calls     : 
**
** Returns   :
** Author    : jocaplan
**
** Date      : 03/30/2009
**
** Parameters: 
                

** Usage     :
               

************************************************************************************
** Change History
************************************************************************************
** Date        Author     Description
** ----------  ---------  ----------------------------------------------------------
**********************************************************************************/

SET NOCOUNT ON

DECLARE
   @ServerName varchar(50)
   ,@CurrentStatus bit
   ,@RC INT
   ,@Counter INT
   ,@NumOfRows INT
   ,@EmailAlias varchar(1000)
   ,@Subject   varchar(1000)
   ,@Message   varchar(max)
   ,@AutoEnable int
   
   
-- Get a list of all of the active servers
SELECT
   Name as ServerName
   ,Offline as CurrentStatus
   ,0 as Status
INTO #ActiveServers
FROM
   [Server] (nolock)
WHERE
   Disabled = 0
   
SELECT @NumOfRows = COUNT(*) FROM #ActiveServers
SELECT @Counter = 0

WHILE @Counter < @NumOfRows
BEGIN

   SELECT TOP 1
      @ServerName = ServerName
      ,@CurrentStatus = CurrentStatus
   FROM
      #ActiveServers
   WHERE
      [Status] = 0
      
   EXEC @RC = CheckServerAvailability @ServerName
   
   IF @RC = 0 AND @CurrentStatus = 1
   BEGIN
   
      -- Check AutoEnable
      SELECT @AutoEnable = ParamNumber
      FROM Parameters
      WHERE ParamName = 'AutoEnable'
      
      IF @AutoEnable = 1
      BEGIN
         UPDATE [Server]
         SET [Offline] = 0
         WHERE
            Name = @ServerName
      END
      ELSE IF @AutoEnable = 0
      BEGIN
         UPDATE [Server]
         SET [Offline] = 0
            ,Disabled = 1 
         WHERE
            Name = @ServerName
      END
      
      -- Send Notification
      -- Get Email Alias
      SELECT @EmailAlias = ParamChar
      FROM Parameters
      WHERE ParamName = 'FailureNotification'
      
      SELECT @Subject = 'ASLB has detected that server ' + @ServerName + ' is back online'
      
      IF @AutoEnable = 0
         SELECT @Message = 'AutoEnable is currently OFF. ' + @ServerName + ' must be manually activated.'
      ELSE
         SELECT @Message = ''
      
      SELECT @Message = @Message + CHAR(13) + 'Current Server and Database Status:'

      SELECT @Message = @Message + CHAR(13) +
         SE.Name
         + '   ' + D.Name
         + '   ' + CASE WHEN SDB.Disabled = 0 THEN 'ONLINE' ELSE 'OFFLINE' END
      FROM
         ServerDatabase SDB
            JOIN [Database] D
               ON SDB.DatabaseID = D.DatabaseID
            JOIN [Server] SE
               ON SDB.ServerID = SE.ServerID
      WHERE
         SE.Disabled = 0
         AND SE.Offline = 0
         
      If ISNULL(@EmailAlias,'') <> ''
      BEGIN
	         
		  -- Send Message
		  EXEC msdb.dbo.sp_send_dbmail
			 @recipients = @EmailAlias
			 ,@Subject = @Subject
			 ,@Body = @Message
      
      END
      
      
   END
   ELSE
   IF @RC = -1
   BEGIN
      -- Check Again
      EXEC @RC = CheckServerAvailability @ServerName
      
      IF @RC = -1
      BEGIN
         -- Take server offline
         UPDATE [Server]
         SET
            [Offline] = 1
         WHERE
            Name = @ServerName
            
         -- Kill the sessions
         
         EXEC EndSessions
            @ServerName = @ServerName
            
         -- Send a notification if this is the first time
         IF @CurrentStatus = 0
         BEGIN
         
            -- Get Email Alias
            SELECT @EmailAlias = ParamChar
            FROM Parameters
            WHERE ParamName = 'FailureNotification'
            
            SELECT @Subject = 'WARNING: ASLB has detected that server ' + @ServerName + ' has gone offline'
            
            SELECT @Message = 'All queries will be directed to other servers' + CHAR(13) + 'Current Server and Database Status:'

            SELECT @Message = @Message + CHAR(13) +
               SE.Name
               + '   ' + D.Name
               + '   ' + CASE WHEN SDB.Disabled = 0 THEN 'ONLINE' ELSE 'OFFLINE' END
            FROM
               ServerDatabase SDB
                  JOIN [Database] D
                     ON SDB.DatabaseID = D.DatabaseID
                  JOIN [Server] SE
                     ON SDB.ServerID = SE.ServerID
            WHERE
               SE.Disabled = 0
               AND SE.Offline = 0
               
            IF ISNULL(@EmailAlias,'') <> ''
            BEGIN
               
				-- Send Message
				EXEC msdb.dbo.sp_send_dbmail
				   @recipients = @EmailAlias
				   ,@Subject = @Subject
				   ,@Body = @Message
	            
            END
         
         
         END
      
      END
   
   END
      
   UPDATE #ActiveServers
   SET Status = 1
   WHERE
      ServerName = @ServerName
      
   Select @Counter = @Counter+1



END